﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using Public;

namespace DAL
{
    public class LoyaltyCardDAL
    {
        DBConnection conn = new DBConnection();
        public int InsertCard(string cusname, string email, string identity, string phone, bool subcribe)
        {
            string sql = "INSERT INTO LoyaltyCard (cusName,phoneNum,identityId,score,level,cusEmail,isFollow) VALUES " +
                "(@cusname,@phone,@identity,@score,@level,@email,@follow)";
            int paramCount = 7;
            string[] paraName = { "cusname", "phone", "identity", "score", "level", "email", "follow" };
            string follow = subcribe ? "1" : "0";
            string[] paraValue = { cusname, phone, identity, "0", "1", email, follow };

            int result = conn.Update(sql, paraName, paraValue, paramCount);
            return result;
        }

        public DataTable LoadAll()
        {
            string sql = "SELECT [cardId] AS CardID,[cusName] AS Name,[phoneNum] AS Phone,[identityId] AS ID,[score] AS Score,"
     + "[level] as Level,[cusEmail] AS Email, [isFollow] as Subcribed  FROM [QuanLyBanHang].[dbo].[LoyaltyCard] ";

            return conn.loadData(sql);
        }

        public DataTable LoadSearch(string searchKey)
        {
            int result;
            if (!int.TryParse(searchKey,out result))
            {
                result = -1;
            }
            string sql = "SELECT [cardId] AS CardID,[cusName] AS Name,[phoneNum] AS Phone,[identityId] AS ID,[score] AS Score,"
     + "[level] as Level,[cusEmail] AS Email, [isFollow] as Subcribed  FROM [QuanLyBanHang].[dbo].[LoyaltyCard] "
     + "Where cardId=" + result + ""
            +" OR cusName like '%" + searchKey + "%'"
            +" OR phoneNum like '" + searchKey + "'"
            +" OR identityId like '" + searchKey + "'"
            +" OR cusEmail like '" + searchKey + "'";

            return conn.loadData(sql);
        }

        public int UpdateCard(int cardId, string email, string phone, bool subcribe)
        {

            string sql = "UPDATE LoyaltyCard SET phoneNum=@phone, cusEmail=@email,isFollow=@follow  WHERE cardID = @cardId";
            int paramCount = 4;
            string[] paraName = { "phone", "email", "follow","cardId" };
            string follow = subcribe ? "1" : "0";
            string[] paraValue = { phone, email, follow,cardId.ToString() };

            int result = conn.Update(sql, paraName, paraValue, paramCount);
            return result;
        }

        public bool CheckDuplicate(string name, string value)
        {
            string sql = "SELECT " + name + " FROM LoyaltyCard WHERE " + name + " like '" + value+  "'";
            return conn.checkExistedItem(sql);
        }

        public int saveAdvertedCus(AdvertedCus advertedCus)
        {
            int Nparameter = 1;
            string[] name = new string[Nparameter];
            object[] value = new object[Nparameter];
            name[0] = "@cardId"; value[0] = advertedCus.LoyaltyCard.CardId;
            string query = "DECLARE @AdvertId int; "+
            "SELECT @AdvertId = (SELECT TOP 1 advertId FROM Advertisement ORDER BY advertId DESC) "+
            "INSERT INTO AdvertedCus(advertId, cardId) VALUES (@AdvertId,@cardId) ";
            return conn.Update(query, name, value, Nparameter);
        }
    }
}
